package com.differ.log.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import com.differ.log.domain.Log;
import com.differ.log.domain.LogContent;

@Repository
public class DifferDao {
	@Autowired
	private JdbcTemplate jdbcTemplate;

	public long insertAndGetGeneratedKeys(final Log log) {
		final String sql = "INSERT INTO `tb_log`(`admin_id`, `type`, `table_id`, `table_name`, `remark`) VALUES ("
				+ log.getAdminId() + "," + log.getType() + "," + log.getTableId() + ",\"" + log.getTableName() + "\",\""
				+ log.getRemark() + "\")";
		System.out.println(sql);
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
				PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				return ps;
			}
		}, keyHolder);
		System.out.println("返回主键：" + keyHolder.getKey().intValue());
		return keyHolder.getKey().intValue();
	}

	public void insertList(final List<LogContent> logContents) {
		jdbcTemplate.batchUpdate(
				"INSERT INTO tb_log_content(log_id, table_column, old_value, new_value, remark) VALUES (?, ?, ?, ?, ?)",
				new BatchPreparedStatementSetter() {
					@Override
					public void setValues(PreparedStatement ps, int i) throws SQLException {
						LogContent logContent = logContents.get(i);
						ps.setLong(1, logContent.getLogId());
						ps.setString(2, logContent.getTableColumn());
						ps.setString(3, logContent.getOldValue());
						ps.setString(4, logContent.getNewValue());
						ps.setString(5, logContent.getRemark());
					}

					@Override
					public int getBatchSize() {
						return logContents.size();
					}
				});
	}

	public List<Log> selectAllLog() {
		return (List<Log>) jdbcTemplate.query("select * from tb_log", new ResultSetExtractor<List<Log>>() {
			public List<Log> extractData(ResultSet rs) throws SQLException, DataAccessException {
				List<Log> customers = new ArrayList<Log>();
				while (rs.next()) {
					Log customer = new Log();
					customer.setId(rs.getLong(1));
					customer.setAdminId(rs.getLong(2));
					customer.setType(rs.getByte(3));
					customer.setTableId(rs.getLong(4));
					customer.setTableName(rs.getString(5));
					customer.setRemark(rs.getString(6));
					customer.setCreatDate(rs.getDate(7));
					customers.add(customer);
				}
				return customers;
			}
		});
	}

	public Log selectLogRespByPrimaryKey(Long id) {
		return (Log) jdbcTemplate.query("select * from tb_log WHERE id="+id, new ResultSetExtractor<Log>() {
			public Log extractData(ResultSet rs) throws SQLException, DataAccessException {
				Log customer = null;
				if (rs.next()) {
					customer = new Log();
					customer.setId(rs.getLong(1));
					customer.setAdminId(rs.getLong(2));
					customer.setType(rs.getByte(3));
					customer.setTableId(rs.getLong(4));
					customer.setTableName(rs.getString(5));
					customer.setRemark(rs.getString(6));
					customer.setCreatDate(rs.getDate(7));
				}
				return customer;
			}
		});
	}

	public List<LogContent> selectLogContentByLogId(Long id) {
		return (List<LogContent>) jdbcTemplate.query("select * from tb_log_content where log_id="+id, new ResultSetExtractor<List<LogContent>>() {
			public List<LogContent> extractData(ResultSet rs) throws SQLException, DataAccessException {
				List<LogContent> customers = new ArrayList<LogContent>();
				while (rs.next()) {
					LogContent customer = new LogContent();
					customer.setId(rs.getLong(1));
					customer.setLogId(rs.getLong(2));
					customer.setTableColumn(rs.getString(3));
					customer.setOldValue(rs.getString(4));
					customer.setNewValue(rs.getString(5));
					customer.setRemark(rs.getString(6));
					customers.add(customer);
				}
				return customers;
			}
		});
	}
}
